﻿using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Smo;

namespace NazTek.Utility.Extensions.SqlObject
{
    /// <summary>
    /// Extends SMO's Table object type.
    /// </summary>
    public static class TableExtension
    {
        /// <summary>
        /// Creates specific <see cref="UniqueKeyIndex"/> instance based on number of participating columns in the first 
        /// unique key index found in the table.
        /// </summary>
        /// <param name="table">Provides the unique key constraints to analyze.</param>
        /// <returns>Null when the table has no unique key constraints, <see cref="UniqueSimpleKeyIndex"/> when it has just 
        /// one unique key constraint, and <see cref="UniqueComplexKeyIndex"/> when the table has multiple
        /// unique key constraints.</returns>
        public static UniqueKeyIndex GetUniqueKeyIndex(this Table table)
        {
            var columns = table.GetUniqueKeyColumns();

            if (columns == null || columns.Count == 0)
                return null;
            if (columns.Count == 1)
                return new UniqueSimpleKeyIndex(columns[0]);
            return new UniqueComplexKeyIndex(columns);
        }
        /// <summary>
        /// Copies a table to a target database.
        /// </summary>
        /// <param name="srcTable">The table to copy.</param>
        /// <param name="targetDatabase">The database where the table is copied to.</param>
        /// <param name="targetTableName">The table's name to set after it's copied.</param>
        /// <param name="publishNotice">Used to publish notice to client before creating table and before naming it.</param>
        public static void CopyTo(this Table srcTable, Database targetDatabase, string targetTableName,
            Action<string> publishNotice)
        {
            var transfer = new Transfer(srcTable.Parent)
            {
                CopyAllObjects = false,
                ObjectList = new ArrayList {srcTable},
                DestinationDatabase = targetDatabase.Name,
                DestinationServer = srcTable.Parent.Parent.Name,
                DestinationLoginSecure = true,
                CopySchema = true,
                CopyData = false,
                PreserveDbo = true
            };
            var scriptBuilder = new StringBuilder();
            foreach (var scriptLine in transfer.ScriptTransfer())
                scriptBuilder.AppendLine(scriptLine);
            publishNotice(string.Format("Executing script [on {0} database]:\n{1}", targetDatabase.Name, scriptBuilder));
            transfer.TransferData();

            if (srcTable.Name == targetTableName)
                return;
            publishNotice(string.Format("Renaming table [{0}].[{1}].[{2}] to [{0}].[{1}].[{3}]", 
                targetDatabase.Name, srcTable.Schema, srcTable.Name, targetTableName));
            targetDatabase.Tables[srcTable.Name, srcTable.Schema].Rename(targetTableName);
        }
        /// <summary>
        /// Ensures two tables are schematically synchronous.
        /// </summary>
        /// <param name="srcTable">Provides source of truth schema.</param>
        /// <param name="targetTable">Schema modified to synchronized changes, if any.</param>
        /// <param name="publishNotice">Used to notify client of specifics during execution steps.</param>
        public static void Sync(this Table srcTable, Table targetTable, Action<string> publishNotice)
        {
            var alterationNeeded = false;
            for (var i = 0; i < srcTable.Columns.Count; i++)
                alterationNeeded = alterationNeeded || srcTable.Columns[i].Sync(targetTable, publishNotice);
            for (var i = targetTable.Columns.Count - 1; i >= 0; i--)
                alterationNeeded = alterationNeeded || srcTable.Columns.Sync(targetTable.Columns, i, publishNotice);
            if (alterationNeeded)
                targetTable.Alter();
        }

        /// <summary>
        /// Gets the list of columns, participating in the first unique key found in the specified table.
        /// </summary>
        /// <param name="table">The table to inspect for unique keys.</param>
        /// <returns>List of columns, participating in the first unique key found in the specified table.</returns>
        public static List<Column> GetUniqueKeyColumns(this TableViewTableTypeBase table)
        {
            var index = table.Indexes.OfType<Index>().FirstOrDefault(idx =>
                idx.IndexKeyType == IndexKeyType.DriPrimaryKey || idx.IndexKeyType == IndexKeyType.DriUniqueKey);
            if (index == null)
                return null;
            var columnList = new List<Column>();
            index.IndexedColumns.OfType<IndexedColumn>().ToList().ForEach(ic => columnList.Add(table.Columns[ic.Name]));
            return columnList;
        }
        /// <summary>
        /// Removes identify columns, if any, from the specified table.
        /// </summary>
        /// <param name="table">Table to inspect for identity columns.</param>
        public static void RemoveIdentityColumns(this TableViewTableTypeBase table)
        {
            var identityColumns = table.Columns.OfType<Column>().Where(col => col.Identity);
            var identityColumnList = identityColumns as List<Column> ?? identityColumns.ToList();
            if (identityColumnList.Any())
                identityColumnList.ForEach(col => col.Identity = false);
        }

        /// <summary>
        /// Adds a column to a specified table.
        /// </summary>
        /// <param name="table">The table to add a column to.</param>
        /// <param name="columnName"></param>
        /// <param name="dataType"></param>
        /// <param name="isNullable"></param>
        /// <param name="defaultValue"></param>
        public static void AddColumn(this TableViewTableTypeBase table, string columnName, DataType dataType,
            bool isNullable = false, string defaultValue = "")
        {
            table.Columns.Add(dataType.GetColumn(table, columnName, isNullable, defaultValue));
        }
    }
}
